import json
import random
import time

import demjson

from com.utils import dbutil
from com.lechebang.spider import LocationUtil


def getCities():
    with open('./autohome_city.json','r',encoding='utf-8') as cityFile:
        tmpjson=cityFile.read()
        tmp2=demjson.decode(tmpjson,"utf-8")
        tmp3=tmp2['AreaInfoGroups']
        provices=[]
        for item in tmp3:
            provices.extend(item['Values'])
        # print(provices)

        allCities=[]
        for item in provices:
            provicine={}
            provicine['FirstChar']=item['FirstChar']
            provicine['Id']=item['Id']
            provicine['Name']=item['Name']
            provicine['Pinyin']=item['Pinyin']
            provicine['Count']=item['Count']
            cities=[]
            citiesdata=item['Cities']
            for item3 in citiesdata:
                city={}
                city['Id'] = item3['Id']
                city['Name'] = item3['Name']
                city['Pinyin'] = item3['Pinyin']
                city['Count'] = item3['Count']
                cities.append(city)
            provicine['cities']=cities
            allCities.append(provicine)
        return allCities


def saveCities(cities,table):
    connection = dbutil.connectDB('lcb_mars_db')
    cursor = connection.cursor()

    for item in cities:
        for item2 in item['cities']:
            insertsql = "insert into %s " \
                    "(province_id,province_name,province_pinyin,province_firstchar,province_count,city_id,city_name,city_pinyin,city_count)" \
                    "values (%d,'%s','%s','%s',%d,%d,'%s','%s',%d)" \
                    % (table, item['Id'],item['Name'],item['Pinyin'],item['FirstChar'],item['Count'],
                       item2['Id'],item2['Name'],item2['Pinyin'],item2['Count'])
            cursor.execute(insertsql)
    connection.commit()

    cursor.close()
    connection.close()

def queryCitiesFromDB(cityTable):
    connection = dbutil.connectDB('lcb_mars_db')
    cursor = connection.cursor()
    cursor.execute('select city_id, city_name,city_pinyin homepage from %s' % cityTable)
    queryResult = cursor.fetchall()
    cities = []
    for row in queryResult:
        city={}
        city['id'] = row[0]
        city['name'] = row[1]
        city['pinyin']=row[2]
        cities.append(city)
    cursor.close()
    connection.close()
    return cities


def getCityCenter(city:str):
    return  LocationUtil.getDistrict(city)


if __name__ == '__main__':
    # allcities=getCities()
    allcities=queryCitiesFromDB('crawler_city')
    # print(allcities)
    con=dbutil.connectDB('lcb_mars_db')
    cursor=con.cursor()
    sql="update crawler_city set center_lat_gd=%s,center_long_gd=%s where city_id=%s"
    for city in allcities:
        tmp=[]
        citydetail=getCityCenter(city['name'])
        long_gd = citydetail['center'].split(',')[0]
        lat_gd = citydetail['center'].split(',')[1]
        # subdistricts=citydetail['subdistricts']
        # print(subdistricts)
        # sql=sql % (lat_gd,long_gd,city['id'])
        tmp.append([lat_gd])
        tmp.append(long_gd)
        tmp.append(city['id'])
        print(tmp)
        result=cursor.execute(sql,tuple(tmp))
        con.commit()
        if result <= 0:
            print("======>执行SQL失败!!, tmp:"+ str(tmp[2]))
        time.sleep(random.uniform(0, 2))  # 随机休息几秒

    cursor.close()
    con.close()
    # saveCities(allcities,'test_city')
